package com.TP.www.util;

import java.io.*;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.*;


/**
 * @author 邱彦祥
 * JDBC
 */
public class JdbcUtils {
    /**
     * 加载驱动路路径
     */
    private static final String URL;
    /**
     * Mysql用户名
     */
    private static final String USERNAME;
    /**
     * 登入密码
     */
    private static final String PASSWORD;
    /**
     * 驱动名
     */
    private static final String DRIVER_CLASS_NAME;
    /**
     * 初始化连接数量
     */
    private static int initialSize ;
    /**
     * 最大活跃数
     */
    private static int maxActive;
    /**
     * 当前使用连接数
     */
    private static int currentCount = 0;
    /**
     * 连接池
     */
    private static final LinkedList<Connection> CONNECTIONS_POOL = new LinkedList<>();

    //静态初始化，加载配置文件及驱动
    static {
        initialSize = 5;
        maxActive = 20;
        Properties prop = new Properties();
        try {
            prop.load(new FileInputStream("src/main/java/com/TP/www/config/JDBC.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }
        URL = prop.getProperty("url");
        USERNAME = prop.getProperty("username");
        PASSWORD = prop.getProperty("password");
        DRIVER_CLASS_NAME = prop.getProperty("driverClassName");
        initialSize = Integer.parseInt(prop.getProperty("initialSize"));
        maxActive = Integer.parseInt(prop.getProperty("maxActive"));
        try{
            //加载驱动
            Class.forName(DRIVER_CLASS_NAME);
            //初始化连接池
            for (int i = 0; i < initialSize; i++) {
                CONNECTIONS_POOL.add(createConnection());
                currentCount++;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 为连接池获取连接器Connection
     * @return Connection
     */
    private static Connection createConnection() throws SQLException {
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }

    /**
     * 从连接池获取连接器Connection
     * @return Connection
     */
    public static Connection getConnection() throws SQLException {
        synchronized (CONNECTIONS_POOL) {
            //当连接池有空闲连接器则直接返回其中的一个连接器
            if (CONNECTIONS_POOL.size() > 0) {
                return CONNECTIONS_POOL.removeFirst();
            } else {
                //当连接池无空闲连接器，且活跃连接器数小于最大连接器数，则新建一个连接器
                if (currentCount < maxActive) {
                    currentCount++;
                    return createConnection();
                } else {
                    throw new SQLException("no more connection.");
                }
            }
        }
    }

    /**
     * 当连接器被使用完毕，则归还连接器回到连接池
     * @param conn 被使用过的连接器
     */
    private static void free(Connection conn) throws SQLException {
        //如果当前连接器数量大于初始化数量，则归还时销毁连接器
        if(currentCount > initialSize){
            conn.close();
            currentCount--;
        }else {
            CONNECTIONS_POOL.addLast(conn);
        }
    }

    /**
     * 获取sql语句执行对象
     * @param connection，sql
     * @return PreparedStatement
     */
    public static PreparedStatement getPreparedStatement(Connection connection,String sql) throws SQLException {
        return connection.prepareStatement(sql);
    }

    /**
     * 关闭所有资源
     * @param connection，preparedStatement，resultSet
     */
    public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
        try {
            if (connection != null){
                free(connection);
            }
            if (preparedStatement != null){
                preparedStatement.close();
            }
            if (resultSet != null){
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 关闭所有资源
     * @param connection，preparedStatement，resultSet
     */
    public static void close(Connection connection, PreparedStatement preparedStatement){
        try {
            if (connection != null){
                connection.close();
            }
            if (preparedStatement != null){
                preparedStatement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 增删改通用操作
     * @param sql  要执行的sql语句
     * @param objects   对象类型数组，内部存放sql语句占位符参数
     */
    public static int executeUpdate(String sql,Object... objects){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int count = -1;
        try {
            //获取连接
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //更改sql语句占位符
            if(objects != null){
                for (int i = 0; i < objects.length; i++) {
                    preparedStatement.setObject(i+1,objects[i]);
                }
            }
            //记录更新条数
            count = preparedStatement.executeUpdate();
            if(count > 0 ){
                return count;
            }
        } catch (Exception e) {
            //处理异常
            e.printStackTrace();
            return count;
        } finally {
            //释放资源
            close(connection,preparedStatement);
        }
        return count;
    }


    /**
     *
     * @param sql sql语句
     * @param t   查询对象
     * @param objects   sql占位符
     * @param <T> 查询对象类型
     * @return  一个装载数据库对象的列表
     */
    public static  <T> List<T> executeQuery(String sql,T t,Object... objects){
        List<T> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //获取连接
        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //更改sql语句占位符
            if(objects != null){
                for (int i = 0; i < objects.length; i++) {
                    preparedStatement.setObject(i+1,objects[i]);
                }
            }
            //执行sql语句
            resultSet = preparedStatement.executeQuery();
            //获取结果集中的所有信息
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            int columnCount = resultSetMetaData.getColumnCount();
            //遍历结果集
            while (resultSet.next()){
                Class<?> cla = t.getClass();
                T newInstance = (T)cla.getDeclaredConstructor().newInstance();
                //一个for循环封装一条记录的所有值
                for(int i=1;i<=columnCount;i++) {
                    //获取字段名
                    String columnName = resultSetMetaData.getColumnName(i);
                    //获取字段对应的setter方法
                    String methodName="set"+columnName.substring(0, 1).toUpperCase()+columnName.substring(1);
                    //获取字段java类型的完全限定名
                    String columnClassName = resultSetMetaData.getColumnClassName(i);
                    //创建方法对象
                    Method method = cla.getDeclaredMethod(methodName, Class.forName(columnClassName));
                    //调用setter方法，执行对象属性赋值
                    method.invoke(newInstance,resultSet.getObject(columnName));
                }
                //将对象加入集合
                list.add(newInstance);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            close(connection,preparedStatement,resultSet);
        }
        return list;
    }
    /**
     *
     * @param sql sql语句
     * @param t   查询对象
     * @param <T> 查询对象类型
     * @return  一个装载数据库对象的列表
     */
    public static  <T> List<T> executeQuery(String sql,T t){
        List<T> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //获取连接
        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //执行sql语句
            resultSet = preparedStatement.executeQuery();
            //获取结果集中的所有信息
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            int columnCount = resultSetMetaData.getColumnCount();
            //遍历结果集
            while (resultSet.next()){
                Class<?> cla = t.getClass();
                T newInstance = (T)cla.getDeclaredConstructor().newInstance();
                //一个for循环封装一条记录的所有值
                for(int i=1;i<=columnCount;i++) {
                    //获取字段名
                    String columnName = resultSetMetaData.getColumnName(i);
                    //获取字段对应的setter方法
                    String methodName="set"+columnName.substring(0, 1).toUpperCase()+columnName.substring(1);
                    //获取字段java类型的完全限定名
                    String columnClassName = resultSetMetaData.getColumnClassName(i);
                    //创建方法对象
                    Method method = cla.getDeclaredMethod(methodName, Class.forName(columnClassName));
                    //调用setter方法，执行对象属性赋值
                    method.invoke(newInstance,resultSet.getObject(columnName));
                }
                //将对象加入集合
                list.add(newInstance);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            close(connection,preparedStatement,resultSet);
        }
        return list;
    }

    /**
     *
     * @param sql sql语句
     * @param t   查询对象
     * @param <T> 查询对象类型
     * @return  一个装载数据库对象的列表
     */
    public static  <T> List<T> executeQueryF(String sql,T t){
        List<T> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //获取连接
        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //执行sql语句
            resultSet = preparedStatement.executeQuery();
            //获取结果集中的所有信息
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            int columnCount = resultSetMetaData.getColumnCount();
            //遍历结果集
            while (resultSet.next()){
                Class<?> cla = t.getClass();
                T newInstance = (T)cla.getDeclaredConstructor().newInstance();
                //一个for循环封装一条记录的所有值
                for(int i=1;i<=columnCount;i++) {
                    //获取字段名
                    String columnName = resultSetMetaData.getColumnName(i);
                    //获取字段对应的setter方法
                    String methodName="set"+columnName.substring(0, 1).toUpperCase()+columnName.substring(1);
                    //获取字段java类型的完全限定名
                    String columnClassName = resultSetMetaData.getColumnClassName(i);
                    //创建方法对象
                    Method method = cla.getMethod(methodName, Class.forName(columnClassName));
                    //调用setter方法，执行对象属性赋值
                    method.invoke(newInstance,resultSet.getObject(columnName));
                }
                //将对象加入集合
                list.add(newInstance);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            close(connection,preparedStatement,resultSet);
        }
        return list;
    }

    /**
     *
     * @param sql sql语句
     * @param t   查询对象
     * @param objects   sql占位符
     * @param <T> 查询对象类型
     * @return  一个装载数据库对象的列表
     */
    public static  <T> List<T> executeQueryF(String sql,T t,Object... objects){
        List<T> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //获取连接
        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //更改sql语句占位符
            if(objects != null){
                for (int i = 0; i < objects.length; i++) {
                    preparedStatement.setObject(i+1,objects[i]);
                }
            }
            //执行sql语句
            resultSet = preparedStatement.executeQuery();
            //获取结果集中的所有信息
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            int columnCount = resultSetMetaData.getColumnCount();
            //遍历结果集
            while (resultSet.next()){
                Class<?> cla = t.getClass();
                T newInstance = (T)cla.getDeclaredConstructor().newInstance();
                //一个for循环封装一条记录的所有值
                for(int i=1;i<=columnCount;i++) {
                    //获取字段名
                    String columnName = resultSetMetaData.getColumnName(i);
                    //获取字段对应的setter方法
                    String methodName="set"+columnName.substring(0, 1).toUpperCase()+columnName.substring(1);
                    //获取字段java类型的完全限定名
                    String columnClassName = resultSetMetaData.getColumnClassName(i);
                    //创建方法对象
                    Method method = cla.getMethod(methodName, Class.forName(columnClassName));
                    //调用setter方法，执行对象属性赋值
                    method.invoke(newInstance,resultSet.getObject(columnName));
                }
                //将对象加入集合
                list.add(newInstance);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            close(connection,preparedStatement,resultSet);
        }
        return list;
    }
}
